import Database from '@tauri-apps/plugin-sql'

class DBClass {
    private db: Database
    private tableName: string | null = null
    private whereStr: string = ''
    private orderStr: string = ''
    private limit: number = 0
    private offset: number = 0
    private field: string = ""
    private whereOrStr: string = ''
    private values: any[] = []

    constructor(db: Database) {
        this.db = db
    }
    private reset(count?: boolean) {
        this.tableName = null
        this.whereStr = ''
        this.orderStr = ''
        this.limit = 0
        this.offset = 0
        this.field = ""
        !count && (this.values = [])
    }
    /**
     * 设置表名
     */
    table(tableName: string) {
        this.tableName = tableName
        return this
    }
    /**
     * 查询所有数据
     */
    async all<T>() {
        let sql = `SELECT`
        sql += this.field ? ` ${this.field}` : ` *`
        sql += ` FROM ${this.tableName}`
        sql += this.orderStr
        const res = await this.db.select<T[]>(sql, this.values)
        this.reset()
        return res
    }
    /**
     * 查询指定条件的第一条数据
     */
    async find<T>(id?: number) {
        let sql = `SELECT`
        sql += this.field ? ` ${this.field}` : ` *`
        sql += ` FROM ${this.tableName}`
        if (id) {
            this.values.push(id)
            sql += ` WHERE id = $${this.values.length}`
        }
        sql += this.whereStr ? ` ${id ? `AND` : `WHERE`} ${this.whereStr}` : ``
        sql += this.whereOrStr ? (this.whereStr ? ` AND (${this.whereOrStr})` : ` WHERE ${this.whereOrStr}`) : ``
        sql += this.orderStr
        const res = await this.db.select<T[]>(sql, this.values)
        this.reset()
        if (res && res.length > 0) {
            return res[0]
        }
        return null
    }
    /**
     * 查询指定条件的所有数据
     */
    async get<T>() {
        let sql = `SELECT`
        sql += this.field ? ` ${this.field}` : ` *`
        sql += ` FROM ${this.tableName}`
        sql += this.whereStr ? ` WHERE ${this.whereStr}` : ``
        sql += this.whereOrStr ? (this.whereStr ? ` AND (${this.whereOrStr})` : ` WHERE ${this.whereOrStr}`) : ``
        sql += this.orderStr
        sql += this.limit ? ` LIMIT ${this.offset}, ${this.limit}` : ``
        const res = await this.db.select<T[]>(sql, this.values)
        this.reset()
        return res
    }
    /**
     * 设置查询条件
     */
    where(...args: [string, string | number | boolean] | [string, string, string | number | boolean] | [[string, string, string | number | boolean]]) {
        let wstr = ''
        if (args.length == 1 && Array.isArray(args[0])) {
            if (args[0][1] !== 'like' && args[0][1] !== '=' && args[0][1] !== '>' && args[0][1] !== '<') {
                throw new Error('参数格式错误')
            }
            this.values.push(args[0][2])
            wstr = `${args[0][0]} ${args[0][1]} $${this.values.length}`
        } else if (args.length == 2) {
            this.values.push(args[1])
            wstr = `${args[0]} = $${this.values.length}`
        } else if (args.length == 3) {
            if (args[1] !== 'like' && args[1] !== '=' && args[1] !== '>' && args[1] !== '<') {
                throw new Error('参数格式错误')
            }
            this.values.push(args[2])
            wstr = `${args[0]} ${args[1]} $${this.values.length}`
        } else {
            throw new Error('参数格式错误')
        }
        this.whereStr += this.whereStr ? ` AND ${wstr}` : `${wstr}`
        return this
    }
    /**
     * 设置或查询条件
     */
    whereOr(...args: [string, string | number | boolean] | [string, string, string | number | boolean] | [[string, string, string | number | boolean]]) {
        let wstr = ''
        if (args.length == 1 && Array.isArray(args[0])) {
            if (args[0][1] !== 'like' && args[0][1] !== '=' && args[0][1] !== '>' && args[0][1] !== '<') {
                throw new Error('参数格式错误')
            }
            this.values.push(args[0][2])
            wstr = `${args[0][0]} ${args[0][1]} $${this.values.length}`
        } else if (args.length == 2) {
            this.values.push(args[1])
            wstr = `${args[0]} = $${this.values.length}`
        } else if (args.length == 3) {
            if (args[1] !== 'like' && args[1] !== '=' && args[1] !== '>' && args[1] !== '<') {
                throw new Error('参数格式错误')
            }
            this.values.push(args[2])
            wstr = `${args[0]} ${args[1]} $${this.values.length}`
        } else {
            throw new Error('参数格式错误')
        }
        this.whereOrStr += this.whereOrStr ? ` OR ${wstr}` : `${wstr}`
        return this
    }
    /**
     * 设置查询条件
     */
    whereBetween(...args: [string, string | number, string | number] | [[string, string | number, string | number]]) {
        let wstr = ''
        if (args.length == 1 && Array.isArray(args[0])) {
            this.values.push(args[0][1])
            const arg1 = this.values.length
            this.values.push(args[0][2])
            const arg2 = this.values.length
            wstr = `${args[0][0]} BETWEEN '$${arg1}' AND '$${arg2}'`
        } else if (args.length == 3) {
            this.values.push(args[1])
            const arg1 = this.values.length
            this.values.push(args[2])
            const arg2 = this.values.length
            wstr = `${args[0]} BETWEEN '$${arg1}' AND '$${arg2}'`
        } else {
            throw new Error('参数格式错误')
        }
        this.whereStr += this.whereStr ? ` AND ${wstr}` : `${wstr}`
        return this
    }
    /**
     * 设置排序
     */
    orderBy(...args: [string, "asc" | "desc"] | [[string, "asc" | "desc"][]]) {
        if (args.length == 1 && Array.isArray(args[0])) {
            this.orderStr += ' ORDER BY ' + args[0].map(order => {
                this.values.push(order[0])
                const order0 = this.values.length
                return `$${order0} ${order[1]}`
            }).join(",")
            return this
        } else if (args.length == 2) {
            this.orderStr += ' ORDER BY '
            // this.values.push(args[0])
            // const order0 = this.values.length
            this.orderStr += `${args[0]} ${args[1]}`
        }
        return this
    }
    /**
     * 分页
     */
    async paginate<T>(page: number, limit: number) {
        const tableName = this.tableName
        const whereStr = this.whereStr
        const orderStr = this.orderStr
        const field = this.field
        const whereOrStr = this.whereOrStr
        const count = await this.count()
        this.tableName = tableName
        this.whereStr = whereStr
        this.orderStr = orderStr
        this.field = field
        this.whereOrStr = whereOrStr
        this.limit = limit
        this.offset = (page - 1) * limit
        const data = await this.get<T>()
        return {
            current_page: page,
            data,
            total: count,
        }
    }
    async count() {
        let sql = `SELECT`
        sql += this.field ? ` COUNT(${this.field})` : ` COUNT(*)`
        sql += ` FROM ${this.tableName}`
        sql += this.whereStr ? ` WHERE ${this.whereStr}` : ``
        const res = await this.db.select<{ 'COUNT(*)': number }[]>(sql, this.values)
        this.reset(true)
        return res[0]['COUNT(*)']
    }

    /**
     * 新增数据
     */
    async create(object: Record<string, any>) {
        if (!this.tableName) throw new Error('请先设置表名')
        delete object.id
        delete object.created_at
        delete object.updated_at
        const keys = Object.keys(object)
        const values = Object.values(object)
        const sql = `INSERT INTO ${this.tableName} (${keys.join(',')}) VALUES (${values.map(v => {
            this.values.push(v)
            return `$${this.values.length}`
        }).join(',')})`
        const res = await this.db.execute(sql, this.values)
        this.reset()
        return res.lastInsertId
    }
    /**
     * 更新数据
     */
    async update(object: Record<string, any>) {
        if (!this.tableName) throw new Error('请先设置表名')
        if (!this.whereStr) throw new Error("请先设置条件")
        delete object.id
        delete object.created_at
        delete object.updated_at
        const data = Object.entries(object)
        let sql = `UPDATE ${this.tableName} SET `
        sql += data.map(key => {
            this.values.push(key[1])
            return `${key[0]} = $${this.values.length}`
        }).join(',')
        sql += ' WHERE ' + this.whereStr
        const res = await this.db.execute(sql, this.values)
        console.log(sql, this.values)
        this.reset()
        return res.lastInsertId
    }
    /**
     * 删除数据
     */
    async delete(id?: number) {
        let sql = `DELETE`
        if (id) {
            this.values.push(id)
            sql += ` FROM ${this.tableName} WHERE id = ${this.values.length}`
        } else {
            sql += ` FROM ${this.tableName}`
            sql += this.whereStr ? ` WHERE ${this.whereStr}` : ``
        }
        const res = await this.db.execute(sql, this.values)
        this.reset()
        return res.rowsAffected
    }
}



// export default new DBClass(db)

export default async function getDb() {
    const db = await Database.load('sqlite:pro-management.data')
    return new DBClass(db)
}